﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;
using ChanTai.WebApi.Data;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NUnit.Framework;

namespace NTest
{
    [TestFixture]
    public class Class1
    {
        [Test]
        public void RedExcel()
        {
            //var map = JsonConvert.DeserializeObject<Dictionary<string, ExeclMapItem>>( File.ReadAllText(@"E:\工作\ChanTai\NTest\bin\Debug\map.txt"));
            //var roatmap= JsonConvert.DeserializeObject < Dictionary<string,Dictionary<string,Dictionary<string,double>>>>(File.ReadAllText(@"E:\工作\ChanTai\NTest\bin\Debug\roat.txt"));
            //var gmap = map["groupacci"];
            //var grout = roatmap["groupacci"];
            //var wk=new Workbook(@"E:\工作\ChanTai\NTest\bin\Debug\GroupAcci.xls");
            //var ct = wk.Worksheets[gmap.SheetPos];
            //Cells cells = ct.Cells;
            //var jar=new JArray();
            //for (int i = 1; i < cells.MaxRow; i++)
            //{
            //    var job=new JObject();
            //    for (int j = 0; j < gmap.Fields.Count; j++)
            //    {
            //        job[gmap.Fields[j]] = cells[i, j].StringValue;
            //    }
            //    jar.Add(job);
            //}
            //var jstr = jar.ToString();
            //Console.WriteLine(jstr);
            //var dt = JsonConvert.DeserializeObject<List<GroupInsureItem>>(jstr);
            //foreach (GroupInsureItem item in dt)
            //{
            //    item.acciRoat = grout["acci"][item.zylb];
            //    item.medicalRoat = grout["medical"][item.zylb];
            //    item.allowanceRoat = grout["allowance"][item.zylb];
            //    item.calculat();
            //}
            var dt = CalculatGroupFromExcel(@"E:\工作\ChanTai\NTest\bin\Debug\GroupAcci.xls", @"E:\工作\ChanTai\NTest\bin\Debug");
            var dtc =
                dt.GroupBy(s => $"{s.zylb}-{s.acciBe}-{s.medicalBe}-{s.allowanceBe}", s => s)
                    .ToDictionary(s => s.Key, s => s.Select(g => g).ToList());
            Console.WriteLine($"groupCount:{dtc.Count}");
            List<Plan> plans=new List<Plan>();
            var smbf = 0d;
            var smbe = 0d;
            var smjf = 0d;
            foreach (var pitem in dtc)
            {
                var tplan = new Plan {itemAcci = new ItemAcci {bationType = false}};
                var p0 = pitem.Value[0];
                tplan.itemAcci.insuredBusinessSource = p0.zylb;
                tplan.itemAcci.quantity = pitem.Value.Count;
                tplan.itemKindList=new List<ItemKind>
                {
                    new ItemKind {kindCode = "1144001",kindInd = "1",rate = p0.acciRoat,sumInsured = p0.acciBe,grossPremium = p0.acciBf}
                };
                if(p0.medicalBe>0) tplan.itemKindList.Add(new ItemKind { kindCode = "1144002", kindInd = "2", rate = p0.medicalRoat, sumInsured = p0.medicalBe, grossPremium = p0.medicalBf });
                if (p0.allowanceBe > 0) tplan.itemKindList.Add(new ItemKind { kindCode = "1144003", kindInd = "2", rate = p0.allowanceRoat, sumInsured = p0.allowanceBe, grossPremium = p0.allowanceBf });
                tplan.riskRealateParyList =new List<RiskRealatePary>();
                foreach (var item in pitem.Value)
                {
                    var risk = new RiskRealatePary
                    {
                        clientCName = item.name,
                        insuredBusinessSource = item.zylb
                    };
                    risk.setInsuredIdNo(item.idno);
                    tplan.riskRealateParyList.Add(risk);
                }
                plans.Add(tplan);
                smbe += p0.totalBe*tplan.itemAcci.quantity;
                smbf += p0.totalBf*tplan.itemAcci.quantity;
                smjf += p0.totalJf*tplan.itemAcci.quantity;
                //Console.WriteLine($"{pitem.Key}\n{JsonConvert.SerializeObject(pitem.Value)}\n\n");
            }
            smbe = Math.Round(smbe, 2);
            smbf = Math.Round(smbf, 2);
            smjf = Math.Round(smjf, 2);
            var grp=new GroupSubmit()
            {
                Main = new AcciMain() { sumInsured = smbe, sumGrossPremium =smbf,commissionRatio = Math.Round(1-smjf/smbf, 2) },
                RelatedParty = new RelatedParty(),
                PlanList = plans
            };
            Console.WriteLine(JsonConvert.SerializeObject(grp));
            //Console.WriteLine(JsonConvert.SerializeObject(dt));

        }

        public List<GroupInsureItem> CalculatGroupFromExcel(string fn, string configpath)
        {
            if(!File.Exists(fn)) throw new Exception("导入数据文件丢失");
            var mpfn = Path.Combine(configpath, @"map.txt");
            var roatfn = Path.Combine(configpath, @"roat.txt");
            if (!File.Exists(mpfn)) throw new Exception("导入数据规则文件丢失");
            if (!File.Exists(roatfn)) throw new Exception("费率配置文件丢失");
            Dictionary<string, ExeclMapItem> map;
            try
            {
                map = JsonConvert.DeserializeObject<Dictionary<string, ExeclMapItem>>(File.ReadAllText(mpfn));
            }
            catch (Exception e)
            {
                throw new Exception("导入数据规则文件格式错误", e);
            }
            Dictionary<string, Dictionary<string, Dictionary<string, RoatItem>>> roatmap;
            try
            {
                roatmap = JsonConvert.DeserializeObject<Dictionary<string, Dictionary<string, Dictionary<string, RoatItem>>>>(File.ReadAllText(roatfn));
            }
            catch (Exception e)
            {
                throw new Exception("费率配置文件格式错误", e);
            }
            
            if(!map.ContainsKey("groupacci")) throw new Exception("未配置团意外险excel解析规则");
            var gmap = map["groupacci"];
            if (!roatmap.ContainsKey("groupacci")) throw new Exception("未配置团意外险费率规则");
            var grout = roatmap["groupacci"];

            if(!grout.ContainsKey("acci")) throw new Exception("未批准主险acci费率");
            if (!grout.ContainsKey("medical")) throw new Exception("附件医疗险medical费率");
            if (!grout.ContainsKey("allowance")) throw new Exception("住院津贴allowance费率");
            var wk = new Workbook(fn);
            var cells = wk.Worksheets[gmap.SheetPos].Cells;
            var jar = new JArray();
            for (int i = 1; i < cells.MaxRow; i++)
            {
                var job = new JObject();
                for (int j = 0; j < gmap.Fields.Count; j++)
                {
                    job[gmap.Fields[j]] = cells[i, j].StringValue;
                }
                jar.Add(job);
            }
            var jstr = jar.ToString();
            //Console.WriteLine(jstr);
            var dt = JsonConvert.DeserializeObject<List<GroupInsureItem>>(jstr);
            var errlist = new List<string>();
            foreach (GroupInsureItem item in dt)
            {
                var ermsg = new List<string>();
                if (!grout["acci"].ContainsKey(item.zylb))
                {
                    ermsg.Add($"主险不保该职业‘{item.zylb}’未配置费率");
                    //throw new Exception($"编号‘{item.id}’主险不保该职业‘{item.zylb}’或未配置费率");
                }
                else item.setAcciRoat(grout["acci"][item.zylb]);
                if (!grout["medical"].ContainsKey(item.zylb))
                {
                    ermsg.Add($"住院医疗险该职业‘{item.zylb}’未配置费率");
                    //throw new Exception($"编号‘{item.id}’主险不保该职业‘{item.zylb}’或未配置费率");
                }
                else item.setMedicalRoat(grout["medical"][item.zylb]);
                if (!grout["allowance"].ContainsKey(item.zylb))
                {
                    ermsg.Add($"住院津贴险该职业‘{item.zylb}’未配置费率");
                    //throw new Exception($"编号‘{item.id}’主险不保该职业‘{item.zylb}’或未配置费率");
                }
                else item.setAallowanceRoatRoat(grout["allowance"][item.zylb]);
                if(ermsg.Count==0) item.calculat();
                else errlist.Add($"编号‘{item.id}’{string.Join(",",ermsg)}");
            }
            if (errlist.Count == 0) return dt;
            throw new Exception($"{string.Join("\n",errlist)}");
        }
        [Test]
        public void xxx()
        {
            var  edt=new DateTime(2017,1,22);
            Console.WriteLine((edt - DateTime.Now.Date).TotalDays);
        }
    }
    
}
